with tab_stop as (
    select
        shipment_no
        ,network_code
        ,roadsection_id
        ,planned_arrival_time
        ,actual_arrival_time
        ,planned_departure_time
        ,actual_departure_time
        ,latest_arrival_time
        ,table_type
    from jms_dwd.dwd_tmsnew_shipment_stop_union_base_dt
    where dt >= date_add('{{ execution_date | cst_ds }}', -29)
),

tab_shipment as (
    select shipment_no,vehicleline_code,vehicleline_name
    from jms_dwd.dwd_tmsnew_shipment_union_base_dt
    where dt >= date_add('{{ execution_date | cst_ds }}', -29)
),

shipment_tmp as (
    SELECT
        shipment_no                   AS shipment_no             -- 转运任务单号
        ,network_code                 AS network_code            -- 网点编码
        ,min(roadsection_id)          AS roadsection_id          -- 路段编号
        ,min(planned_arrival_time)    AS planned_arrival_time    -- 计划到达日期+时间
        ,min(actual_arrival_time)     AS actual_arrival_time     -- 实际到达时间
        ,min(planned_departure_time)  AS planned_departure_time  -- 计划离开日期+时间
        ,min(actual_departure_time)   AS actual_departure_time   -- 实际发车时间
        ,min(latest_arrival_time)     AS latest_arrival_time     -- 最晚到车时间+日期
        ,min(table_type)              AS table_type              -- 支干线类型
        ,min(vehicleline_code)        AS vehicleline_code        -- 参考车线编码
        ,min(vehicleline_name)        AS vehicleline_name        -- 参考车线名称
    FROM (
          -- SELECT shipment_no, network_code, roadsection_id, planned_arrival_time,actual_arrival_time,planned_departure_time,actual_departure_time,null as latest_arrival_time
          -- FROM jms_ods.yl_tmsnew_branch_shipment_stop WHERE dt = '{{ execution_date | cst_ds }}'
          -- AND (actual_arrival_time >= date_add('{{ execution_date | cst_ds }}', -29) OR actual_departure_time >= date_add('{{ execution_date | cst_ds }}', -29) OR
          -- planned_arrival_time >= date_add('{{ execution_date | cst_ds }}', -29) OR planned_departure_time >= date_add('{{ execution_date | cst_ds }}', -29))
          -- UNION ALL
          -- SELECT shipment_no, network_code, roadsection_id, planned_arrival_time,actual_arrival_time,planned_departure_time,actual_departure_time,latest_arrival_time
          -- FROM jms_ods.yl_tmsnew_tms_shipment_stop
          -- WHERE dt = '{{ execution_date | cst_ds }}'
          -- AND (actual_arrival_time >= date_add('{{ execution_date | cst_ds }}', -29) OR actual_departure_time >= date_add('{{ execution_date | cst_ds }}', -29) OR
          -- planned_arrival_time >= date_add('{{ execution_date | cst_ds }}', -29) OR planned_departure_time >= date_add('{{ execution_date | cst_ds }}', -29) OR
          -- latest_arrival_time >= date_add('{{ execution_date | cst_ds }}', -29))
          -- 取数改造,由全量ODS表改造为增量DWD合并表，并增加支干线类型字段。
          select
              t1.shipment_no
              ,network_code
              ,roadsection_id
              ,planned_arrival_time
              ,actual_arrival_time
              ,planned_departure_time
              ,actual_departure_time
              ,latest_arrival_time
              ,table_type
              ,vehicleline_code
              ,vehicleline_name
          from tab_stop t1
          left join tab_shipment t2
          on t1.shipment_no = t2.shipment_no
    ) as  stops
    GROUP BY shipment_no, network_code
)

insert overwrite table jms_dwd.dwd_wide_rank_basic_scaninfo_tms_dt partition(dt)
select
    trace_main.waybill_no
    ,trace_main.step
    ,trace_main.scan_type
    ,trace_main.network_code
    ,trace_main.network_type
    ,trace_main.network_scan_time_early
    ,trace_main.scan_time
    ,trace_main.step_reserve
    ,trace_main.prev_network_code
    ,trace_main.prev_network_type
    ,trace_main.next_network_code
    ,trace_main.next_network_type
    ,trace_main.step_center
    ,trace_main.step_center_reserve
    ,trace_main.step_entrepot
    ,trace_main.step_entrepot_reserve
    ,trace_main.recordid
    ,trace_main.packagecode
    ,trace_main.nextstationcode
    ,trace_main.nextstation
    ,trace_main.destination
    ,trace_main.scanusercode
    ,trace_main.scanuser
    ,trace_main.source
    ,trace_main.weight
    ,trace_main.expresstype
    ,trace_main.returnflag
    ,trace_main.elescaleflag
    ,trace_main.pistolid
    ,trace_main.shipment_no
    ,trace_main.network_name
    ,trace_main.is_sign
    ,trace_main.sendcustomer
    ,shipment_tmp.roadsection_id  as roadsection_id
    ,shipment_tmp.planned_arrival_time  as planned_arrival_time
    ,shipment_tmp.actual_arrival_time  as actual_arrival_time
    ,shipment_tmp.planned_departure_time  as planned_departure_time
    ,shipment_tmp.actual_departure_time  as actual_departure_time
    ,shipment_tmp.latest_arrival_time  as latest_arrival_time
    ,from_unixtime(unix_timestamp(),"yyyy-MM-dd HH:mm:ss") as update_time
    ,shipment_tmp.table_type
    ,shipment_tmp.vehicleline_code
    ,shipment_tmp.vehicleline_name
    ,trace_main.subbillcode
    ,trace_main.dt
 from (
    select
        waybill_no
        ,step
        ,scan_type
        ,network_code
        ,network_type
        ,network_scan_time_early
        ,scan_time
        ,step_reserve
        ,prev_network_code
        ,prev_network_type
        ,next_network_code
        ,next_network_type
        ,step_center
        ,step_center_reserve
        ,step_entrepot
        ,step_entrepot_reserve
        ,recordid
        ,packagecode
        ,nextstationcode
        ,nextstation
        ,destination
        ,scanusercode
        ,scanuser
        ,source
        ,weight
        ,expresstype
        ,returnflag
        ,elescaleflag
        ,pistolid
        ,shipment_no
        ,network_name
        ,is_sign
        ,sendcustomer
        ,subbillcode
        ,dt
    from jms_dwd.dwd_wide_rank_basic_scaninfo_dt
    where dt between date_add('{{ execution_date | cst_ds }}', -29)
    and date_add('{{ execution_date | cst_ds }}', -15)

    union all

    select
        waybill_no
        ,step
        ,scan_type
        ,network_code
        ,network_type
        ,network_scan_time_early
        ,scan_time
        ,step_reserve
        ,prev_network_code
        ,prev_network_type
        ,next_network_code
        ,next_network_type
        ,step_center
        ,step_center_reserve
        ,step_entrepot
        ,step_entrepot_reserve
        ,recordid
        ,packagecode
        ,nextstationcode
        ,nextstation
        ,destination
        ,scanusercode
        ,scanuser
        ,source
        ,weight
        ,expresstype
        ,returnflag
        ,elescaleflag
        ,pistolid
        ,shipment_no
        ,network_name
        ,is_sign
        ,sendcustomer
        ,subbillcode
        ,dt
    from jms_dwd.dwd_wide_rank_basic_scaninfo_dt
    where dt = '2049-12-31'
) trace_main
left join shipment_tmp
on nvl(trace_main.shipment_no,trace_main.waybill_no) = shipment_tmp.shipment_no
and nvl(trace_main.network_code,trace_main.waybill_no) = shipment_tmp.network_code;